import pandas as pd

# 读取Excel文件
pd.set_option("display.unicode.east_asian_width", True)
# allMes = pd.read_csv("成绩.CSV", encoding="gbk")  # 原始成绩表格
allMes = pd.read_excel("成绩.xlsx")  # 原始成绩表格
# print(allMes.head())
major = allMes["专业"].unique()  # 专业信息
# print(major)
for i in major:
    numberUp = allMes.loc[allMes["专业"] == i, ['学号', '姓名']].drop_duplicates()
    studentnum = []
    names = []
    allsum = []
    allcredit = []
    lastcredit = []
    for index, t in numberUp.iterrows():
        j = t['学号']
        name = t["姓名"]
        # print(name)
        creditall = allMes.loc[allMes['学号'] == j, ['学分', '成绩']]
        # print(creditall.head())
        sumup = (creditall['学分'] * creditall['成绩']).sum()
        # print(sumup)
        creditup = creditall['学分'].sum()
        # print(creditup)
        weightgrade = sumup / creditup
        studentnum.append(j)
        names.append(name)
        allsum.append(sumup)
        allcredit.append(creditup)
        lastcredit.append(weightgrade)
        # print("学号：", j)
        # print("姓名", name)
        # print("总学分", allcredit)
        # print("加权成绩", weightgrade)
        # print("******************")
    df1 = pd.DataFrame({
        "学号": studentnum,
        "姓名": names,
        "权重成绩综合": allsum,
        "总学分": allcredit,
        "加权成绩": lastcredit,
    })
    df1.to_csv(i + '.CSV', index=False, encoding="utf_8_sig")
